package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.PracticePlan;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.response.PracticeCheckRade;
import com.xmy.cultivate.entity.response.PracticePlanStaff;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 练课进度 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-10-09
 */
public interface PracticePlanMapper extends BaseMapper<PracticePlan> {


    @Select({"<script>",
            "SELECT",
                      "*,",
                      "getCourseTypeName ( class_type_id ) AS classs_type_name,",
                      "count( id ) AS show_pass_num,",
                      "SUM( CASE WHEN finish_num &gt; 0 THEN finish_num ELSE 0 END ) AS yet_pass_num,",
                      "SUM( CASE WHEN finish_num = 0 THEN 1 ELSE 0 END ) AS not_pass_num,",
                      "SUM( CASE WHEN finish_num = 1 THEN score ELSE 0 END ) AS all_score",
                    "FROM",
                      "(",
                      "SELECT",
                        "t01.*,",
                        "getSubjectsName ( t01.subjects_id ) AS subjects_name,",
                        "getDictName ( t01.year_class_id, 14 ) AS year_class_name,",
                        "t02.lesson_num,",
                        "IFNULL( t03.finish_num, 0 ) AS finish_num,",
                        "t03.option_value,",
                        "IFNULL( t03.score, 0 ) AS score,",
                        "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date,",
                        "t04.type",
                      "FROM",
                        "(",
                        "SELECT",
                          "t1.id,",
                          "t1.school_id,",
                          "t1.teacher_id,",
                          "t1.year_class_id,",
                          "t1.subjects_id,",
                          "t1.course_type_id AS class_type_id,",
                          "t1.year_part,",
                          "t1.quarter_num,",
                          "t1.status,",
                          "t1.finish_date,",
                          "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,",
                          "t2.NAME AS teacher_name,",
                          "(",
                          "SELECT",
                            "count( id ) ",
                          "FROM",
                            "student_grade AS stu_g ",
                          "WHERE",
                            "stu_g.grade_id = t1.id ",
                            "AND stu_g.is_last = 1 ",
                            "AND ((",
                                "stu_g.reading_status = 1 ",
                                ") ",
                            "OR ( stu_g.reading_status = 5 AND stu_g.use_course_count &gt; 0 ))) AS stu_grade_count,",
                          "1 as teacher_type",
                        "FROM",
                          "grade AS t1",
                          "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                        "WHERE",
                          "t1.deleted = 0 ",
                          "AND t1.year_part = ${yearPart} ",
                          "AND t1.quarter_num = ${quarterNum} ",
                          "AND t1.lesson_type = ${lessonType} ",
                            "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                                "AND t1.year_class_id in(${yearClassId}) ",
                            "</when>",
                        "<when test='teacherName!=null and teacherName!= &apos;&apos;'>",
                            "AND t2.name like '%${teacherName}%'",
                        "</when>",
                        "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                          "AND t1.course_type_id in (${classTypeId}) ",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                          "AND t1.subjects_id in(${subjectsId}) ",
                        "</when>",
                        //"<when test='schoolId!=null and schoolId!= &apos;&apos;'>",
                        //"AND t1.school_id in(${schoolId}) ",
                        //"</when>",
                        "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
                            "AND (t1.school_id in(${schoolId}) or t1.teacher_id in(${inTeacherId})) ",
                        "</when>",
                        "GROUP BY",
                          "t1.teacher_id,",
                          "t1.subjects_id,",
                          "t1.year_class_id,",
                          "t1.course_type_id ",
                        "UNION ALL",
                            "SELECT",
                            "t1.id,",
                            "t1.school_id,",
                            "t1.teacher_id,",
                            "t1.year_class_id,",
                            "t1.subjects_id,",
                            "t1.course_type_id AS class_type_id,",
                            "t1.year_part,",
                            "t1.quarter_num,",
                            "1 AS `status`,",
                            "NULL AS finish_date,",
                            "NULL AS grade_created_at,",
                            "t2.NAME AS teacher_name,",
                            "NULL AS stu_grade_count,",
                            "t1.teacher_type ",
                            "FROM",
                            "practice_brush_teacher AS t1",
                            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                        "WHERE",
                            "t1.deleted = 0 ",
                            "AND t1.year_part = ${yearPart} ",
                            "AND t1.quarter_num = ${quarterNum} ",
                            "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                            "AND t1.year_class_id in(${yearClassId}) ",
                            "</when>",
                            "<when test='teacherName!=null and teacherName!= &apos;&apos;'>",
                            "AND t2.name like '%${teacherName}%'",
                            "</when>",
                            "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                            "AND t1.course_type_id in (${classTypeId}) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                            "AND t1.subjects_id in(${subjectsId}) ",
                            "</when>",
                            //"<when test='schoolId!=null and schoolId!= &apos;&apos;'>",
                            //"AND t1.school_id in(${schoolId}) ",
                            //"</when>",
                            "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
                            "AND (t1.school_id in(${schoolId}) or t1.teacher_id in(${inTeacherId})) ",
                            "</when>",
                            "GROUP BY",
                            "t1.teacher_id,",
                            "t1.subjects_id,",
                            "t1.year_class_id,",
                            "t1.course_type_id,",
                            "t1.teacher_type",
                        ") AS t01",
                        "LEFT JOIN rade_exclude_teacher as t08 on t01.year_part = t08.year_part and t01.quarter_num = t08.quarter_num and t01.teacher_id = t08.teacher_id and t08.type =1",
                        "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
                        "AND t01.quarter_num = t02.quarter_num ",
                        "AND t02.deleted = 0 ",
                        "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
                        "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd}",
                        "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
                        "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
                        "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
                        "AND t04.id = ${practiceTypeId}",
                        "</when>",
                        "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
                        "AND t01.quarter_num = t03.quarter_num ",
                        "AND t01.year_class_id = t03.year_class_id ",
                        "AND t01.subjects_id = t03.subjects_id ",
                        "AND t02.lesson_num = t03.lesson_num ",
                        "AND t01.class_type_id = t03.class_type_id ",
                        "AND t03.deleted = t02.deleted ",
                        "AND t01.teacher_id = t03.teacher_id ",
                        "and t03.practice_type_id = t05.practice_type_id",
                        "where t08.id is null",
                        ") AS t ",
                    "GROUP BY",
                      "teacher_id",
            "</script>"})
    public IPage<PracticePlan> getEnterList(IPage<?> iPage,
                                           @Param("yearPart") Integer yearPart,
                                           @Param("quarterNum") Integer quarterNum,
                                           @Param("schoolId") String schoolId,
                                           @Param("lessonType") Integer lessonType,
                                           @Param("yearClassId") String yearClassId,
                                           @Param("classTypeId") String classTypeId,
                                           @Param("subjectsId") String subjectsId,
                                           @Param("lessonNumStart") Integer lessonNumStart,
                                           @Param("lessonNumEnd") Integer lessonNumEnd,
                                           @Param("practiceTypeId") Long practiceTypeId,
                                           @Param("teacherName") String teacherName,
                                           @Param("inTeacherId") Long inTeacherId
                                           );

    @Select({"<script>",
            "SELECT",
                        "*,",
                        "getCourseTypeName ( class_type_id ) AS classs_type_name,",
                        "count( id ) AS show_pass_num,",
                        "SUM( CASE WHEN finish_num &gt; 0 THEN finish_num ELSE 0 END ) AS yet_pass_num,",
                        "SUM( CASE WHEN finish_num = 0 THEN 1 ELSE 0 END ) AS not_pass_num,",
                        "SUM( CASE WHEN finish_num = 1 THEN score ELSE 0 END ) AS all_score",
                    "FROM",
                      "(",
                      "SELECT",
                        "t01.*,",
                        "getSubjectsName ( t01.subjects_id ) AS subjects_name,",
                        "getDictName ( t01.year_class_id, 14 ) AS year_class_name,",
                        "t02.lesson_num,",
                        "IFNULL( t03.finish_num, 0 ) AS finish_num,",
                        "t03.option_value,",
                        "IFNULL( t03.score, 0 ) AS score,",
                        "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date,",
                        "t04.type ",
                      "FROM",
                        "(",
                        "SELECT",
                          "t1.id,t1.school_id,t1.teacher_id,t1.year_class_id,t1.subjects_id,t1.course_type_id AS class_type_id,t1.year_part,t1.quarter_num,",
                          "t2.NAME AS teacher_name ",
                        "FROM",
                          "practice_brush_teacher AS t1",
                          "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id and t1.deleted = 0",
                        "WHERE",
                            "t1.deleted = 0 ",
                            "AND t1.year_part = ${yearPart} ",
                            "AND t1.quarter_num = ${quarterNum} ",
                            "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                            "AND t1.year_class_id in(${yearClassId}) ",
                            "</when>",
                            "<when test='teacherName!=null and teacherName!= &apos;&apos;'>",
                            "AND t2.name like '%${teacherName}%'",
                            "</when>",
                            "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                            "AND t1.course_type_id in (${classTypeId}) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                            "AND t1.subjects_id in(${subjectsId}) ",
                            "</when>",
                            //"<when test='schoolId!=null and schoolId!= &apos;&apos;'>",
                            //"AND t1.school_id in(${schoolId}) ",
                            //"</when>",
                            "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
                            "AND (t1.school_id in(${schoolId}) or t1.teacher_id in(${inTeacherId})) ",
                            "</when>",
                        "GROUP BY",
                          "t1.subjects_id,",
                          "t1.year_class_id,",
                          "t1.course_type_id,",
                          "t1.teacher_id,",
                          "t1.teacher_type",
                        ") AS t01",
                        "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
                        "AND t01.quarter_num = t02.quarter_num ",
                        "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd}",
                        "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
                        "AND t01.quarter_num = t03.quarter_num ",
                        "AND t01.year_class_id = t03.year_class_id ",
                        "AND t01.subjects_id = t03.subjects_id ",
                        "AND t02.lesson_num = t03.lesson_num ",
                        "AND t01.class_type_id = t03.class_type_id ",
                        "AND t03.deleted = t02.deleted ",
                        "AND t01.teacher_id = t03.teacher_id ",
                        "AND t03.practice_type_id = ${practiceTypeId} ",
                        "LEFT JOIN practice_type AS t04 ON t03.practice_type_id = t04.id  OR t04.id = ${practiceTypeId} ",
                        ") AS t ",
                    "GROUP BY",
                    "teacher_id",
            "</script>"})
    public IPage<PracticePlan> getEnterForTeacherList(IPage<?> iPage,
                                            @Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("schoolId") String schoolId,
                                            @Param("yearClassId") String yearClassId,
                                            @Param("classTypeId") String classTypeId,
                                            @Param("subjectsId") String subjectsId,
                                            @Param("lessonNumStart") Integer lessonNumStart,
                                            @Param("lessonNumEnd") Integer lessonNumEnd,
                                            @Param("practiceTypeId") Long practiceTypeId,
                                            @Param("teacherName") String teacherName,
                                            @Param("inTeacherId") Long inTeacherId
    );


    @Select({"<script>",
            "select * from (",
            "SELECT",
                      "t01.*,",
                      "getSubjectsName ( t01.subjects_id ) AS subjects_name,",
                      "getDictName ( t01.year_class_id, 14 ) AS year_class_name,",
                      "getCourseTypeName(t01.class_type_id) as class_type_name,",
                      "t02.lesson_num,",
                      "IFNULL( t03.finish_num, 0 ) AS finish_num,",
                      "t03.option_value,",
                      "IFNULL( t03.score, 0 ) AS score,",
                      "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date,",
                      "t04.id AS practice_type_id,",
                      "t04.NAME AS practice_type_name,",
                      "t04.type,",
                      "t06.name as audit_teacher_name",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.id,",
                        "t1.school_id,",
                        "t1.teacher_id,",
                        "t1.year_class_id,",
                        "t1.subjects_id,",
                        "t1.course_type_id AS class_type_id,",
                        "t1.year_part,",
                        "t1.quarter_num,",
                        "t1.status,",
                        "t1.finish_date,",
                        "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,",
                        "t2.NAME AS teacher_name,",
                        "(",
                        "SELECT",
                          "count( id ) ",
                        "FROM",
                          "student_grade AS stu_g ",
                        "WHERE",
                          "stu_g.grade_id = t1.id ",
                          "AND stu_g.is_last = 1 ",
                          "AND ((",
                              "stu_g.reading_status = 1 ",
                              ") ",
                          "OR ( stu_g.reading_status = 5 AND stu_g.use_course_count &gt; 0 ))) AS stu_grade_count,",
                        "1 as teacher_type",
                        "FROM",
                        "grade AS t1",
                        "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                      "WHERE",
                        "t1.deleted = 0 ",
                        "AND t1.year_part = ${yearPart} ",
                        "AND t1.quarter_num = ${quarterNum} ",
                        "AND t1.lesson_type = ${lessonType} ",
                        "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                        "AND t1.year_class_id in(${yearClassId}) ",
                        "</when>",
                        "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                        "AND t1.course_type_id in(${classTypeId}) ",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                        "AND t1.subjects_id in(${subjectsId}) ",
                        "</when>",
                        "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
                            "AND (t1.teacher_id in(${inTeacherId})) ",
                        "</when>",
                      "GROUP BY",
                        "t1.teacher_id,",
                        "t1.subjects_id,",
                        "t1.year_class_id,",
                        "t1.course_type_id ",
                    "UNION ALL",
                        "SELECT",
                        "t1.id,",
                        "t1.school_id,",
                        "t1.teacher_id,",
                        "t1.year_class_id,",
                        "t1.subjects_id,",
                        "t1.course_type_id AS class_type_id,",
                        "t1.year_part,",
                        "t1.quarter_num,",
                        "1 AS `status`,",
                        "NULL AS finish_date,",
                        "NULL AS grade_created_at,",
                        "t2.NAME AS teacher_name,",
                        "NULL AS stu_grade_count,",
                        "t1.teacher_type ",
                    "FROM",
                        "practice_brush_teacher AS t1",
                    "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                    "WHERE",
                        "t1.deleted = 0 ",
                        "AND t1.year_part = ${yearPart} ",
                        "AND t1.quarter_num = ${quarterNum} ",
                        "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                        "AND t1.year_class_id in(${yearClassId}) ",
                        "</when>",
                        "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                        "AND t1.course_type_id in(${classTypeId}) ",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                        "AND t1.subjects_id in(${subjectsId}) ",
                        "</when>",
                        "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
                        "AND (t1.teacher_id in(${inTeacherId})) ",
                        "</when>",
                        "GROUP BY",
                        "t1.teacher_id,",
                        "t1.subjects_id,",
                        "t1.year_class_id,",
                        "t1.course_type_id,",
                        "t1.teacher_type",
                      ") AS t01",
                      "LEFT JOIN rade_exclude_teacher AS t08 ON t01.year_part = t08.year_part  AND t01.quarter_num = t08.quarter_num AND t01.teacher_id = t08.teacher_id AND t08.type = 1",
                      "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
                      "AND t01.quarter_num = t02.quarter_num ",
                      "AND t02.deleted = 0 ",
                      "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
                      "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd}",
                        "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
                        "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
                        "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
                        "AND t04.id = ${practiceTypeId}",
                        "</when>",
                        "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
                      "AND t01.quarter_num = t03.quarter_num ",
                      "AND t01.year_class_id = t03.year_class_id ",
                      "AND t01.subjects_id = t03.subjects_id ",
                      "AND t02.lesson_num = t03.lesson_num ",
                      "AND t01.class_type_id = t03.class_type_id ",
                      "AND t03.deleted = t02.deleted ",
                      "AND t01.teacher_id = t03.teacher_id ",
                      "and t03.practice_type_id = t05.practice_type_id",
                      "LEFT JOIN staff as t06 on t03.audit_teacher_id = t06.id",
                      "WHERE t08.id IS NULL ",
                       "ORDER BY",
                      "t03.lesson_num,",
                      "t01.year_class_id,",
                      "t01.id ASC",
                ") as t",
                "<when test='isFinish!=null and isFinish!= &apos;&apos;'>",
                "where finish_num = ${isFinish} ",
                "</when>",
            "</script>"})
    public IPage<PracticePlan> getEnterDetail(IPage<?> iPage,
                                           @Param("yearPart") Integer yearPart,
                                           @Param("quarterNum") Integer quarterNum,
                                           @Param("schoolId") String schoolId,
                                           @Param("lessonType") Integer lessonType,
                                           @Param("yearClassId") String yearClassId,
                                           @Param("classTypeId") String classTypeId,
                                           @Param("subjectsId") String subjectsId,
                                           @Param("lessonNumStart") Integer lessonNumStart,
                                           @Param("lessonNumEnd") Integer lessonNumEnd,
                                           @Param("practiceTypeId") Long practiceTypeId,
                                           @Param("inTeacherId") Long inTeacherId,
                                           @Param("isFinish") String isFinish
    );

    @Select({"<script>",
            "SELECT",
                      "* ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t01.*,",
                        "getSubjectsName ( t01.subjects_id ) AS subjects_name,",
                        "getDictName ( t01.year_class_id, 14 ) AS year_class_name,",
                        "getCourseTypeName ( t01.class_type_id ) AS class_type_name,",
                        "t02.lesson_num,",
                        "IFNULL( t03.finish_num, 0 ) AS finish_num,",
                        "t03.option_value,",
                        "IFNULL( t03.score, 0 ) AS score,",
                        "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date,",
                        "t04.id AS practice_type_id,",
                        "t04.NAME AS practice_type_name,",
                        "t04.type,",
                        "t05.NAME AS audit_teacher_name ",
                      "FROM",
                        "(",
                        "SELECT",
                          "t1.id,t1.school_id,t1.teacher_id,t1.year_class_id,t1.subjects_id,t1.course_type_id AS class_type_id,t1.year_part,t1.quarter_num,",
                          "t2.NAME AS teacher_name ",
                        "FROM",
                          "practice_brush_teacher AS t1",
                          "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id and t1.deleted = 0",
                        "WHERE",
                            "t1.deleted = 0 ",
                            "AND t1.year_part = ${yearPart} ",
                            "AND t1.quarter_num = ${quarterNum} ",
                            "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                            "AND t1.year_class_id in(${yearClassId}) ",
                            "</when>",
                            "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                            "AND t1.course_type_id in(${classTypeId}) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                            "AND t1.subjects_id in(${subjectsId}) ",
                            "</when>",
                            "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
                            "AND (t1.teacher_id in(${inTeacherId})) ",
                            "</when>",
                        "GROUP BY",
                          "t1.teacher_id,",
                          "t1.subjects_id,",
                          "t1.year_class_id,",
                          "t1.course_type_id,",
                          "t1.teacher_type",
                        ") AS t01",
                        "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
                        "AND t01.quarter_num = t02.quarter_num ",
                        "AND t02.lesson_num BETWEEN ${lessonNumStart}  AND ${lessonNumEnd}",
                        "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
                        "AND t01.quarter_num = t03.quarter_num ",
                        "AND t01.year_class_id = t03.year_class_id ",
                        "AND t01.subjects_id = t03.subjects_id ",
                        "AND t02.lesson_num = t03.lesson_num ",
                        "AND t01.class_type_id = t03.class_type_id ",
                        "AND t03.deleted = t02.deleted ",
                        "AND t01.teacher_id = t03.teacher_id ",
                        "AND t03.practice_type_id = ${practiceTypeId}",
                        "LEFT JOIN staff AS t05 ON t03.audit_teacher_id = t05.id",
                        "LEFT JOIN practice_type AS t04 ON t03.practice_type_id = t04.id ",
                        "OR t04.id = ${practiceTypeId} ",
                      "ORDER BY",
                        "t03.lesson_num,",
                        "t01.year_class_id,",
                      "t01.id ASC ",
                      ") AS t",
            "</script>"})
    public IPage<PracticePlan> getEnterForTeacherDetail(IPage<?> iPage,
                                              @Param("yearPart") Integer yearPart,
                                              @Param("quarterNum") Integer quarterNum,
                                              @Param("schoolId") String schoolId,
                                              @Param("lessonType") Integer lessonType,
                                              @Param("yearClassId") String yearClassId,
                                              @Param("classTypeId") String classTypeId,
                                              @Param("subjectsId") String subjectsId,
                                              @Param("lessonNumStart") Integer lessonNumStart,
                                              @Param("lessonNumEnd") Integer lessonNumEnd,
                                              @Param("practiceTypeId") Long practiceTypeId,
                                              @Param("inTeacherId") Long inTeacherId,
                                              @Param("isFinish") String isFinish
    );


    @Select({"<script>",
            "SELECT ",
                      "t1.id, ",
                      "t1.year_part, ",
                      "t1.`quarter_num`, ",
                      "t1.year_class_id, ",
                      "t1.lesson_num, ",
                      "t1.subjects_id, ",
                      "t1.teacher_id, ",
                      "t1.class_type_id, ",
                      "t1.practice_type_id, ",
                      "t1.is_check, ",
                      "t1.created_at, ",
                      "t3.`name` AS teacher_name, ",
                      "getDictName ( t1.year_class_id, 14 ) AS year_class_name, ",
                      "getCourseTypeName ( t1.class_type_id ) AS class_type_name, ",
                      "getSubjectsName ( t1.subjects_id ) AS subjects_name, ",
                      "t4.NAME AS practice_type_name  ",
                    "FROM ",
                      "practice_plan AS t1 ",
                      "INNER JOIN staff AS t3 ON t1.teacher_id = t3.id ",
                      "INNER JOIN practice_type AS t4 ON t1.practice_type_id = t4.id ",
                      "INNER JOIN practice_check_teacher AS t2 ON t1.year_part = t2.year_part  ",
                      "AND t1.quarter_num = t2.quarter_num  ",
                      "AND t1.teacher_id = t2.teacher_id  ",
                      "AND ( FIND_IN_SET( t1.year_class_id, t2.year_class_id ) OR t2.year_class_id IS NULL )  ",
                      "AND ( FIND_IN_SET( t1.subjects_id, t2.subjects_id ) OR t2.subjects_id IS NULL )  ",
                      "AND ( FIND_IN_SET( t1.class_type_id, t2.class_type_id ) OR t2.class_type_id IS NULL )  ",
                      "AND ( FIND_IN_SET( t1.practice_type_id, t2.practice_type_id ) OR t2.practice_type_id IS NULL )",
                      "where 1=1",
                    "<when test='yearPart!=null and yearPart!= &apos;&apos;'>",
                      "and t1.year_part = ${yearPart}",
                    "</when>",
                    "<when test='quarterNum!=null and quarterNum!= &apos;&apos;'>",
                      "and t1.quarter_num = ${quarterNum}",
                    "</when>",
                    "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                      "and t1.year_class_id in(${yearClassId})",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                      "and t1.class_type_id in(${classTypeId})",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                      "and t1.subjects_id in(${subjectsId})",
                    "</when>",
                    "<when test='practiceTypeId!=null and practiceTypeId!= &apos;&apos;'>",
                      "and t1.practice_type_id in(${practiceTypeId})",
                    "</when>",
                    "<when test='(lessonNumStart!=null and lessonNumStart!= &apos;&apos;) and (lessonNumEnd!=null and lessonNumEnd!= &apos;&apos;)'>",
                      "and t1.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd}",
                    "</when>",
                    "<when test='isCheck!=null and isCheck!= &apos;&apos; and isCheck == 0'>",
                      "and t1.is_check = ${isCheck}",
                    "</when>",
                    "<when test='isCheck!=null and isCheck!= &apos;&apos; and isCheck > 0'>",
                    "and t1.is_check &gt; 0",
                    "</when>",
                    "<when test='checkTeacherId!=null and checkTeacherId!= &apos;&apos; and checkTeacherId != &quot;1568138350324350975&quot; '>",
                      "and t2.check_teacher_id = ${checkTeacherId}",
                    "</when>",
                    "<when test='(starDate!=null and starDate!= &apos;&apos;) and (endDate!=null and endDate!= &apos;&apos;)'>",
                      "and t1.created_at BETWEEN #{starDate} AND #{endDate}",
                    "</when>",
                    "order by t1.created_at desc",
            "</script>"})
    public IPage<PracticePlan> getCheckList(IPage<?> iPage,
                                            @Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("yearClassId") String yearClassId,
                                            @Param("classTypeId") String classTypeId,
                                            @Param("subjectsId") String subjectsId,
                                            @Param("lessonNumStart") Integer lessonNumStart,
                                            @Param("lessonNumEnd") Integer lessonNumEnd,
                                            @Param("practiceTypeId") Long practiceTypeId,
                                            @Param("checkTeacherId") Long checkTeacherId,
                                            @Param("isCheck") String isCheck,
                                            @Param("starDate") String starDate,
                                            @Param("endDate") String endDate
                                            );


    @Select({"<script>",
            "SELECT",
                      "*,",
                      "SUM( CASE WHEN plan_id IS NOT NULL THEN 1 ELSE 0 END ) AS show_check_count,",
                      "SUM( CASE WHEN plan_id IS NOT NULL AND is_check > 0 THEN 1 ELSE 0 END ) AS yet_check_count,",
                      "SUM( CASE WHEN plan_id IS NOT NULL AND is_check = 0 THEN 1 ELSE 0 END ) AS not_check_count ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.year_part,",
                        "t1.quarter_num,",
                        "t1.teacher_id,",
                        "t1.check_teacher_id,",
                        "t2.id AS plan_id,",
                        "t2.is_check,",
                        "t2.finish_num,",
                        "t3.name as  check_teacher_name",
                      "FROM",
                        "practice_check_teacher AS t1",
                        "INNER JOIN staff as t3 on t1.check_teacher_id = t3.id",
                        "LEFT JOIN practice_plan AS t2 ON t1.year_part = t2.year_part ",
                        "AND t1.quarter_num = t2.quarter_num ",
                        "AND t1.teacher_id = t2.teacher_id ",
                        "AND t1.deleted = t2.deleted ",
                        "AND ( FIND_IN_SET( t2.year_class_id, t1.year_class_id ) OR t1.year_class_id IS NULL ) ",
                        "AND ( FIND_IN_SET( t2.subjects_id, t1.subjects_id ) OR t1.subjects_id IS NULL ) ",
                        "AND ( FIND_IN_SET( t2.class_type_id, t1.class_type_id ) OR t1.class_type_id IS NULL ) ",
                        "AND ( FIND_IN_SET( t2.practice_type_id, t1.practice_type_id ) OR t1.practice_type_id IS NULL ) ",
                      "WHERE",
                        "t1.deleted = 0 ",
                        "<when test='yearPart!=null and yearPart!= &apos;&apos;'>",
                            "and t1.year_part = ${yearPart}",
                        "</when>",
                        "<when test='quarterNum!=null and quarterNum!= &apos;&apos;'>",
                            "and t1.quarter_num = ${quarterNum}",
                        "</when>",
                        "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                            "and t2.year_class_id in(${yearClassId})",
                        "</when>",
                        "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                            "and t2.class_type_id in(${classTypeId})",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                            "and t2.subjects_id in(${subjectsId})",
                        "</when>",
                        "<when test='practiceTypeId!=null and practiceTypeId!= &apos;&apos;'>",
                            "and t2.practice_type_id in(${practiceTypeId})",
                        "</when>",
                        "<when test='(lessonNumStart!=null and lessonNumStart!= &apos;&apos;) and (lessonNumEnd!=null and lessonNumEnd!= &apos;&apos;)'>",
                            "and t2.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd}",
                        "</when>",
                        "<when test='(startDate!=null and startDate!= &apos;&apos;) and (endDate!=null and endDate!= &apos;&apos;)'>",
                        "and t2.created_at BETWEEN #{startDate} AND #{endDate}",
                        "</when>",
                        "<when test='teacherName!=null and teacherName!= &apos;&apos;'>",
                            "and t3.name like '%${teacherName}%'",
                        "</when>",
                      ") AS t ",
                    "GROUP BY",
                      "check_teacher_id",
            "</script>"
    })
    public List<PracticeCheckRade> checkRadeList(@Param("yearPart") String yearPart,
                                                  @Param("quarterNum") String quarterNum,
                                                  @Param("lessonNumStart") String lessonNumStart,
                                                  @Param("lessonNumEnd") String lessonNumEnd,
                                                  @Param("startDate") String startDate,
                                                  @Param("endDate") String endDate,
                                                  @Param("type") String type,
                                                  @Param("yearClassId") String yearClassId,
                                                  @Param("classTypeId") String classTypeId,
                                                  @Param("subjectsId") String subjectsId,
                                                  @Param("practiceTypeId") String practiceTypeId,
                                                  @Param("teacherName") String teacherName,
                                                  @Param("lessonType") String lessonType,
                                                  @Param("schoolId") String schoolId);

    @Select({"<script>",
            "SELECT",
                    "t1.year_part,",
                    "t1.quarter_num,",
                    "t1.teacher_id,",
                    "t1.check_teacher_id,",
                    "t2.id AS plan_id,",
                    "t2.is_check,",
                    "t2.finish_num,",
                    "t3.NAME AS teacher_name,",
                    "t2.year_class_id,",
                    "t2.class_type_id,",
                    "t2.lesson_num,",
                    "t2.practice_type_id,",
                    "getDictName ( t2.year_class_id, 14 ) AS year_class_name,",
                    "getCourseTypeName ( t2.class_type_id ) AS class_type_name,",
                    "t4.NAME AS practice_type_name ",
                    "FROM",
                    "practice_check_teacher AS t1",
                    "LEFT JOIN practice_plan AS t2 ON t1.year_part = t2.year_part ",
                    "AND t1.quarter_num = t2.quarter_num ",
                    "AND t1.teacher_id = t2.teacher_id ",
                    "AND t1.deleted = t2.deleted ",
                    "AND ( FIND_IN_SET( t2.year_class_id, t1.year_class_id ) OR t1.year_class_id IS NULL ) ",
                    "AND ( FIND_IN_SET( t2.subjects_id, t1.subjects_id ) OR t1.subjects_id IS NULL ) ",
                    "AND ( FIND_IN_SET( t2.class_type_id, t1.class_type_id ) OR t1.class_type_id IS NULL ) ",
                    "AND ( FIND_IN_SET( t2.practice_type_id, t1.practice_type_id ) OR t1.practice_type_id IS NULL )",
                    "LEFT JOIN practice_type AS t4 ON t2.practice_type_id = t4.id ",
                    "LEFT JOIN staff AS t3 ON t2.teacher_id = t3.id",
                    "WHERE",
                    "t1.deleted = 0 ",
                    "<when test='yearPart!=null and yearPart!= &apos;&apos;'>",
                    "and t1.year_part = ${yearPart}",
                    "</when>",
                    "<when test='quarterNum!=null and quarterNum!= &apos;&apos;'>",
                    "and t1.quarter_num = ${quarterNum}",
                    "</when>",
                    "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                    "and t2.year_class_id in(${yearClassId})",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                    "and t2.class_type_id in(${classTypeId})",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                    "and t2.subjects_id in(${subjectsId})",
                    "</when>",
                    "<when test='practiceTypeId!=null and practiceTypeId!= &apos;&apos;'>",
                    "and t2.practice_type_id in(${practiceTypeId})",
                    "</when>",
                    "<when test='(lessonNumStart!=null and lessonNumStart!= &apos;&apos;) and (lessonNumEnd!=null and lessonNumEnd!= &apos;&apos;)'>",
                    "and t2.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd}",
                    "</when>",
                    "<when test='(startDate!=null and startDate!= &apos;&apos;) and (endDate!=null and endDate!= &apos;&apos;)'>",
                    "and t2.created_at BETWEEN #{startDate} AND #{endDate}",
                    "</when>",
                    "<when test='teacherId!=null and teacherId!= &apos;&apos;'>",
                    "and t1.check_teacher_id = ${teacherId}",
                    "</when>",
                    "<when test='isCheck!=null and isCheck!= &apos;&apos; and isCheck == 0'>",
                        "and t2.is_check = ${isCheck}",
                    "</when>",
                    "<when test='isCheck!=null and isCheck!= &apos;&apos; and isCheck > 0'>",
                        "and t2.is_check &gt; 0",
                    "</when>",
            "</script>"})
    public IPage<PracticeCheckRade> checkRadeDetail(IPage<?> page,
                                                 @Param("yearPart") String yearPart,
                                                 @Param("quarterNum") String quarterNum,
                                                 @Param("lessonNumStart") String lessonNumStart,
                                                 @Param("lessonNumEnd") String lessonNumEnd,
                                                 @Param("startDate") String startDate,
                                                 @Param("endDate") String endDate,
                                                 @Param("type") String type,
                                                 @Param("yearClassId") String yearClassId,
                                                 @Param("classTypeId") String classTypeId,
                                                 @Param("subjectsId") String subjectsId,
                                                 @Param("practiceTypeId") String practiceTypeId,
                                                 @Param("teacherId") String teacherId,
                                                 @Param("lessonType") String lessonType,
                                                 @Param("schoolId") String schoolId,
                                                 @Param("isCheck") String isCheck
                                                    );

}
